<?php
$gameSql = "SELECT * FROM ".TABLEPREFIX."_game WHERE is_active='Y'";
$gameArr = $UserManagerObjAjax->GetRecords("All",$gameSql);
$Numgame = count($gameArr);
for($g=0;$g<$Numgame;$g++)
{
  $query="SELECT count(ug.game_id) as cnt  FROM ".TABLEPREFIX."_user u,".TABLEPREFIX."_user_type_user_relation ur,".TABLEPREFIX."_user_game ug WHERE 
  ug.game_id='".$gameArr[$g]['game_id']."' AND ur.user_type_id=2 AND ur.user_id=u.user_id AND u.user_id=ug.user_id";
  $Arr = $UserManagerObjAjax->GetRecords("Row",$query);
  $gameArr[$g]['cnt'] = $Arr['cnt'];
} 

$availSql = "SELECT count(u.user_id) FROM ".TABLEPREFIX."_user u,".TABLEPREFIX."_user_type_user_relation ur WHERE u.availability_type='O' AND ur.user_type_id=2 AND 
ur.user_id= u.user_id";
$avail1Arr = $UserManagerObjAjax->GetRecords("Row",$availSql);
$avail1Num = $avail1Arr[0];

$availSql =  "SELECT count(u.user_id) FROM ".TABLEPREFIX."_user u,".TABLEPREFIX."_user_type_user_relation ur WHERE u.availability_type='L' AND ur.user_type_id=2 AND 
ur.user_id= u.user_id";
$avail2Arr = $UserManagerObjAjax->GetRecords("Row",$availSql);
$avail2Num = $avail2Arr[0];


$languageSql = "SELECT lng.language_id AS language_id, lng.language_name AS language_name, count( lng.language_id ) AS cnt
FROM ".TABLEPREFIX."_language lng
JOIN ".TABLEPREFIX."_user st ON FIND_IN_SET( lng.language_id, st.language_ids )
AND lng.is_active = 'Y'
AND FIND_IN_SET( lng.language_id, (
SELECT u.language_ids
FROM ".TABLEPREFIX."_user u, ".TABLEPREFIX."_user_type_user_relation ur
WHERE u.is_active = 'Y'
AND ur.user_type_id =2
AND ur.user_id = u.user_id
AND u.user_id = st.user_id
) )
GROUP BY lng.language_id, lng.language_name
ORDER BY lng.language_name ASC ";
$languageArr = $UserManagerObjAjax->GetRecords("All",$languageSql);
$Numlanguage = count($languageArr);
$availcoutSql = "SELECT country_id,country_name FROM ".TABLEPREFIX."_country ORDER BY country_name";
$AvailcounArr = $UserManagerObjAjax->HtmlOptionArrayCreate($availcoutSql);



$smarty->assign('Numgame',$Numgame);
$smarty->assign('gameArr',$gameArr);
$smarty->assign('avail1Num',$avail1Num);
$smarty->assign('avail2Num',$avail2Num);
$smarty->assign('Numlanguage',$Numlanguage);
$smarty->assign('languageArr',$languageArr);
$smarty->assign('AvailcounArr',$AvailcounArr);

$smarty->display('student_leftpanel.tpl');   

?>